package com.automation.monkeytalk;

import java.io.File;
import java.io.FileInputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Locale;

import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;

import org.joda.time.DateTime;
import org.junit.Test;

public class ExecuteOracleStoredProcedure extends BaseClass {

  static String DB_DRIVER;
  static String DB_CONNECTION;
  static String DB_USER;
  static String DB_PASSWORD;


  @Test
  public void testRunStoredProcedure() throws Exception {
    try {

      log("info", "Started Stored procedure test...");
      String testDataExcel =
          System.getProperty("user.dir") + "/TestData/TD_OracleStoredProcedureInfo.xls";

      FileInputStream fs = null;
      WorkbookSettings ws = null;
      fs = new FileInputStream(new File(testDataExcel));
      ws = new WorkbookSettings();
      ws.setLocale(new Locale("en", "EN"));
      Workbook TDworkbook = Workbook.getWorkbook(fs, ws);
      Sheet TDSheet = TDworkbook.getSheet(0);
      log("info", "No of rows to iterate=." + TDSheet.getRows());

      for (int k = 1; k < TDSheet.getRows(); k++) {
        if (TDSheet.getCell(0, k).getContents().equalsIgnoreCase("N")) {
          continue;
        }


        // Read the columns...
        DB_DRIVER = TDSheet.getCell(2, k).getContents();
        DB_CONNECTION = TDSheet.getCell(3, k).getContents();
        DB_USER = TDSheet.getCell(4, k).getContents();
        DB_PASSWORD = TDSheet.getCell(5, k).getContents();
        log("info", "Test Data read from spreadsheet row no " + (k + 1));
        execStoredProcedure();


      }
      fs.close();
      log("info", "Ending test...");
    } catch (Exception e) {
      log("error", "Exception in running stored procedure:" + e.getMessage());
    }
  }


  public void execStoredProcedure() throws SQLException {
    log("info", "Executing stored procedure...");
    Connection dbConnection = null;
    CallableStatement callableStatement = null;

    String storedProcedure = "{call OFS_REFRESH.POPULATEALL(?)}";

    try {
      dbConnection = getDBConnection();
      callableStatement = dbConnection.prepareCall(storedProcedure);
      DateTime time = new DateTime(2000, 05, 06, 00, 00, 00);
      callableStatement.setDate(1, new java.sql.Date(time.getMillis()));

      log("info", "started executing procedure  ");
      long startTime = System.currentTimeMillis();
      callableStatement.executeUpdate();
      log("info", "End of procedure ... time taken " + (System.currentTimeMillis() - startTime)
          / 1000);

      /*
       * String userName = callableStatement.getString(2); String createdBy =
       * callableStatement.getString(3); Date createdDate = callableStatement.getDate(4);
       * 
       * System.out.println("UserName : " + userName); System.out.println("CreatedBy : " +
       * createdBy); System.out.println("CreatedDate : " + createdDate);
       */

      log("info", "Stored procedure Execution completed...");

    } catch (SQLException e) {

      log("error", "SQL Exception:" + e.getMessage());

    } finally {

      if (callableStatement != null) {
        callableStatement.close();
      }

      if (dbConnection != null) {
        dbConnection.close();
      }

    }


  }


  public Connection getDBConnection() {

    Connection dbConnection = null;

    try {

      Class.forName(DB_DRIVER);

    } catch (ClassNotFoundException e) {

      log("error", e.getMessage());

    }

    try {

      dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
      return dbConnection;

    } catch (SQLException e) {

      log("error", "Exception making connection " + e.getMessage());

    }

    return dbConnection;

  }


}
